# -*- coding: utf-8 -*-
import datetime
import time

import pymysql
import threading
from concurrent.futures import ThreadPoolExecutor


# 致远数据库操作
def seeyon_query(sql):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='seeyona8',
                           charset='utf8')  # 链接数据库
    cursor = conn.cursor()
    cursor.execute(sql)
    Pricing_metthod = cursor.fetchall()
    cursor.close()
    conn.close()
    return Pricing_metthod

def seeyon_param_query(sql, params=None):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='seeyona8',
                           charset='utf8')
    cursor = conn.cursor()
    cursor.execute(sql, params or ())
    Pricing_metthod = cursor.fetchall()
    conn.close()
    return Pricing_metthod


def seeyon_execute(sql):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='seeyona8',
                           charset='utf8')  # 链接数据库
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        conn.commit()
        print('数据更新成功')
    except Exception as e:
        print("出现错误:" + str(e))
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

def seeyon_param_execute(sql, params=None):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='seeyona8',
                           charset='utf8')  # 链接数据库
    cursor = conn.cursor()
    try:
        cursor.execute(sql, params or ())
        conn.commit()
        print('数据更新成功')
    except Exception as e:
        print("出现错误:" + str(e))
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


# zkteco数据库操作
def zkteco_query(sql, params=None):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='zktecokem',
                           charset='utf8')  # 链接数据库
    cursor = conn.cursor()
    cursor.execute(sql, params)
    Pricing_metthod = cursor.fetchall()
    cursor.close()
    conn.close()
    return Pricing_metthod

# zkteco删除操作
def zkteco_delete(sql, params=None):
    conn = None
    cursor = None
    try:
        conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='zktecokem', charset='utf8')
        cursor = conn.cursor()
        cursor.execute(sql, params)
        conn.commit()
        deleted_count = cursor.rowcount
        return deleted_count
    except:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()



def zkteco_execute(sql, params=None):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='zktecokem',
                           charset='utf8')  # 链接数据库
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql, params or ())
            conn.commit()
            return cursor.rowcount
    except:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()


def zkteco_executemany(sql, params_list):
    conn = pymysql.connect(host='192.168.0.13', port=3308, user='seeyon', password='pwd98765', db='zktecokem',
                           charset='utf8')  # 链接数据库
    try:
        with conn.cursor() as cursor:
            cursor.executemany(sql, params_list)
            conn.commit()
            return cursor.rowcount
    except:
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()


# 批量检查记录是否存在
def batch_check_records_exist(records):
    query = "SELECT USERID, CHECKTIME FROM checkinout WHERE USERID = %s AND CHECKTIME = %s"
    params_list = [(record['userid'], record['checktime']) for record in records]

    def check_record(params):
        result = zkteco_query(query, params)
        return params if not result else None

    with ThreadPoolExecutor() as executor:
        results = list(executor.map(check_record, params_list))

    # 过滤掉已存在的记录
    return [params for params in results if params is not None]


# 批量插入考勤记录
def batch_insert_attendance_records(records):
    # 先过滤已存在的记录
    records_to_insert = batch_check_records_exist(records)
    if not records_to_insert:
        print("所有记录已存在，无需插入")
        return 0
    insert_sql = """
        INSERT INTO checkinout (
            USERID, CHECKTIME, CHECKTYPE, VERIFYCODE, SENSORID,
            MemoInfo, WorkCode, sn, UserExtFmt, mask_flag, temperature
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    # 准备插入参数
    params_list = [
        (
            userid,
            checktime,
            'I', 15, None,  # CHECKTYPE, VERIFYCODE, SENSORID
            None, None, None, None,  # MemoInfo, WorkCode, sn, UserExtFmt
            None, 0  # mask_flag, temperature
        )
        for (userid, checktime) in records_to_insert
    ]

    try:
        time.sleep(5)
        inserted_count = zkteco_executemany(insert_sql, params_list)
        print(f"成功插入 {inserted_count} 条新记录")
        return inserted_count
    except Exception as e:
        print(f"批量插入出错: {e}")
        return 0


# 批量删除考勤记录
def batch_delete_attendance_records(records):
    query = "SELECT USERID, CHECKTIME FROM checkinout WHERE USERID = %s AND CHECKTIME = %s"
    params_list = [(record['userid'], record['checktime']) for record in records]

    def check_record(params):
        result = zkteco_query(query, params)
        return params if result else None

    with ThreadPoolExecutor() as executor:
        results = list(executor.map(check_record, params_list))

    # 只保留存在的记录
    records_to_delete = [params for params in results if params is not None]

    if not records_to_delete:
        print("没有找到需要删除的记录")
        return 0

    delete_sql = "DELETE FROM checkinout WHERE USERID = %s AND CHECKTIME = %s"

    try:
        deleted_count = zkteco_executemany(delete_sql, records_to_delete)
        print(f"成功删除 {deleted_count} 条记录")
        return deleted_count
    except Exception as e:
        print(f"批量删除出错: {e}")
        return 0


if __name__ == '__main__':
    # 示例使用
    records_to_insert = [
        {'userid': '321', 'checktime': '2025-06-05 11:47:32'},
        {'userid': '321', 'checktime': '2025-06-05 12:38:25'},
        {'userid': '321', 'checktime': '2025-06-05 17:02:07'},
        {'userid': '321', 'checktime': '2025-06-06 07:39:38'},
        {'userid': '321', 'checktime': '2025-06-06 12:03:00'},
        {'userid': '321', 'checktime': '2025-06-06 12:39:11'},
        {'userid': '321', 'checktime': '2025-06-06 17:02:22'},
        {'userid': '321', 'checktime': '2025-06-06 17:26:15'},
        {'userid': '321', 'checktime': '2025-06-06 20:33:14'},
        {'userid': '321', 'checktime': '2025-06-07 07:38:42'},
        {'userid': '321', 'checktime': '2025-06-07 11:59:55'},
        {'userid': '321', 'checktime': '2025-06-07 12:38:50'},
        {'userid': '321', 'checktime': '2025-06-09 07:38:54'},
        {'userid': '321', 'checktime': '2025-06-09 12:04:03'},
        {'userid': '321', 'checktime': '2025-06-09 12:40:15'},
        {'userid': '321', 'checktime': '2025-06-09 17:29:14'},
        {'userid': '321', 'checktime': '2025-06-09 17:20:38'},
        {'userid': '321', 'checktime': '2025-06-09 20:30:54'},
        {'userid': '321', 'checktime': '2025-06-10 07:38:50'},
        {'userid': '321', 'checktime': '2025-06-10 12:26:00'},
        {'userid': '321', 'checktime': '2025-06-10 13:11:00'},
        {'userid': '321', 'checktime': '2025-06-10 17:03:30'},
        {'userid': '321', 'checktime': '2025-06-11 07:40:59'},
        {'userid': '321', 'checktime': '2025-06-11 11:58:52'},
        {'userid': '321', 'checktime': '2025-06-11 12:40:19'},
        {'userid': '321', 'checktime': '2025-06-15 07:09:46'},
        {'userid': '321', 'checktime': '2025-06-16 06:47:32'},
        {'userid': '321', 'checktime': '2025-06-15 12:00:15'},
        {'userid': '321', 'checktime': '2025-06-15 17:10:38'},
        {'userid': '321', 'checktime': '2025-06-15 12:37:56'},
        {'userid': '321', 'checktime': '2025-06-12 07:44:19'},
        {'userid': '321', 'checktime': '2025-06-13 07:43:31'},
        {'userid': '321', 'checktime': '2025-06-13 18:31:37'},
        {'userid': '321', 'checktime': '2025-06-14 07:39:54'},
        {'userid': '321', 'checktime': '2025-06-12 17:27:28'},
        {'userid': '321', 'checktime': '2025-06-11 17:10:07'},
        {'userid': '321', 'checktime': '2025-06-11 18:03:23'},
        {'userid': '321', 'checktime': '2025-06-12 12:02:36'},
        {'userid': '321', 'checktime': '2025-06-12 17:18:33'},
        {'userid': '321', 'checktime': '2025-06-13 12:04:47'},
        {'userid': '321', 'checktime': '2025-06-14 11:57:52'},
        {'userid': '321', 'checktime': '2025-06-14 17:13:19'},
        {'userid': '321', 'checktime': '2025-06-11 17:24:57'},
        {'userid': '321', 'checktime': '2025-06-12 12:40:31'},
        {'userid': '321', 'checktime': '2025-06-12 18:03:36'},
        {'userid': '321', 'checktime': '2025-06-13 12:39:56'},
        {'userid': '321', 'checktime': '2025-06-14 12:38:21'},
        {'userid': '321', 'checktime': '2025-06-16 12:04:48'},
        {'userid': '321', 'checktime': '2025-06-16 12:39:52'},
        {'userid': '321', 'checktime': '2025-06-16 17:12:35'},
        {'userid': '321', 'checktime': '2025-06-17 06:50:30'},
        {'userid': '321', 'checktime': '2025-06-17 12:05:53'},
        {'userid': '321', 'checktime': '2025-06-17 12:39:53'},
        {'userid': '321', 'checktime': '2025-06-17 17:10:47'},
        {'userid': '321', 'checktime': '2025-06-18 06:49:06'},
        {'userid': '321', 'checktime': '2025-06-18 12:39:45'},
        {'userid': '321', 'checktime': '2025-06-18 17:26:07'},
        {'userid': '321', 'checktime': '2025-06-19 06:47:40'},
        {'userid': '321', 'checktime': '2025-06-19 12:39:38'},
        {'userid': '321', 'checktime': '2025-06-19 19:01:48'},
        {'userid': '321', 'checktime': '2025-06-19 17:26:40'},
        {'userid': '321', 'checktime': '2025-06-20 06:46:35'},
        {'userid': '321', 'checktime': '2025-06-20 12:39:15'},
        {'userid': '321', 'checktime': '2025-06-23 06:57:36'},
        {'userid': '321', 'checktime': '2025-06-23 12:54:06'},
        {'userid': '321', 'checktime': '2025-06-23 17:26:47'},
        {'userid': '321', 'checktime': '2025-06-24 07:20:32'},
        {'userid': '321', 'checktime': '2025-06-25 12:57:34'},
        {'userid': '321', 'checktime': '2025-06-25 20:02:20'},
        {'userid': '321', 'checktime': '2025-06-26 06:55:55'},
        {'userid': '321', 'checktime': '2025-06-26 13:09:09'},
        {'userid': '321', 'checktime': '2025-06-27 06:57:01'},
        {'userid': '321', 'checktime': '2025-06-27 17:25:54'},
        {'userid': '321', 'checktime': '2025-06-28 06:56:56'},
        {'userid': '321', 'checktime': '2025-06-30 06:57:16'},
        {'userid': '321', 'checktime': '2025-06-30 17:26:18'},
        {'userid': '321', 'checktime': '2025-06-30 18:00:46'},
        {'userid': '321', 'checktime': '2025-07-01 13:09:45'},
        {'userid': '321', 'checktime': '2025-07-01 06:58:21'},

    ]

    batch_insert_attendance_records(records_to_insert)

    # records_to_delete = [
    #     {'userid': 2358, 'checktime': '2025-04-01 00:22:47'},
    #     {'userid': 2442, 'checktime': '2025-04-01 00:22:52'},
    #     {'userid': 1064, 'checktime': '2025-04-01 00:30:17'}
    # ]

